package DataLayer.DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DataLayer.Model.Service;

public class DBService implements IFDBService
{
	private Connection con = DBConnection.getInstance().getDBcon();

	@Override
	public ArrayList<Service> getAllService(boolean synlig)
	{
		int s = (synlig ? 1 : 0);
		return miscWhere("synlig = '" + s + "'");
	}
	
	public ArrayList<Service> getAllService()
	{
		return miscWhere("");
	}

	@Override
	public ArrayList<Service> searchServiceViaSpil(String spil, boolean synlig)
	{
		// Implemented for later use
		String wClause = " spil = '" + spil + "'";
		return miscWhere(wClause);
	}

	@Override
	public ArrayList<Service> searchServiceViaModel(String model, boolean synlig)
	{
		// Implemented for later use
		String wClause = " model = '" + model + "'," +
				" synlig = '1'";
		return miscWhere(wClause);
	}

	@Override
	public Service getService(int serviceId)
	{
		String wClause = " serviceId = '" + serviceId + "'";
		return singleWhere(wClause);
	}

	@Override
	public int insertService(Service s) throws Exception
	{
		int nextID = GetMax.getMaxId("SELECT MAX(serviceId) FROM Service");
		nextID = nextID + 1;
		int rc = -1;
		String query = "INSERT INTO Service(serviceId, spil, model, malestandard, pris, synlig)  VALUES('"
				+
				nextID + "','" +
				s.getSpil() + "','" +
				s.getModel() + "','" +
				s.getMalestandard() + "','" +
				s.getPris() + "','" +
				(s.isSynlig() ? 1 : 0) + "')";
		System.out.println("insert : " + query);
		try
		{ // insert new Service
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (SQLException ex)
		{
			System.out.println("Service was inserted");
		}
		return (rc);
	}

	@Override
	public int updateService(Service service)
	{
		Service s = service;
		int rc = -1;
		String query = "UPDATE Service SET " +
				"spil ='" + s.getSpil() + "', " +
				"model ='" + s.getModel() + "', " +
				"malestandard ='" + s.getMalestandard() + "', " +
				"pris ='" + s.getPris() + "', " +
				"synlig ='" + (s.isSynlig() ? 1 : 0) + "'" +
				" WHERE serviceId = '" + s.getServiceId() + "'";
		System.out.println("Update query:" + query);
		try
		{ // update Service
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// slut try
		catch (Exception ex)
		{
			System.out.println("Update exception in Service db: " + ex);
		}
		return (rc);
	}

	@Override
	public int deleteService(int serviceId)
	{
		int rc = -1;
		String query = "DELETE FROM Service WHERE serviceId = '" +
				serviceId + "'";
		System.out.println(query);
		try
		{ // delete FROM Service
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// slut try
		catch (Exception ex)
		{
			System.out.println("Delete exception in Service db: " + ex);
		}
		return (rc);
	}

	private ArrayList<Service> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Service> list = new ArrayList<Service>();
		String query = buildQuery(wClause);
		query += " ORDER by spil, model";
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next())
			{
				Service s = new Service();
				s = buildService(results);
				list.add(s);
			}
			stmt.close();
		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Service singleWhere(String wClause)
	{
		ResultSet results;
		Service s = new Service();
		String query = buildQuery(wClause);
		System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next())
			{
				s = buildService(results);
				stmt.close();
			}
			else
			{
				s = null;
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return s;
	}

	private String buildQuery(String wClause)
	{
		String query = "SELECT serviceId, spil, model, malestandard, pris, synlig FROM Service ";
		if (wClause.length() > 0)
		{
			query += "WHERE " + wClause;
		}
		return query;
	}

	/**
	 * builds an object s of the class Service
	 * 
	 * @return Service
	 */
	private Service buildService(ResultSet results)
	{
		Service s = new Service();
		try
		{
			s.setServiceId(results.getInt("serviceId"));
			s.setSpil(results.getString("spil"));
			s.setModel(results.getString("model"));
			s.setMalestandard(results.getString("malestandard"));
			s.setPris(results.getDouble("pris"));
			s.setSynlig(results.getBoolean("synlig"));
		}
		catch (Exception e)
		{
			System.out.println("error in building the Service object");
		}
		return s;
	}
}